#! /bin/bash
#SQOOP_HOME=/opt/cloudera/parcels/CDH-6.2.1-1.cdh6.2.1.p0.1425774/bin/sqoop
HIVE_HOME=/usr/bin/hive
if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi

${HIVE_HOME} -S -e "
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;

set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;

set hive.exec.orc.compression.strategy=COMPRESSION;


with sign as(select
    id,
    normal_class_flag,
    time_table_id,
    class_id,
    student_id,
    substr(signin_time,1,19) as signin_time,
    signin_date,
    inner_flag,
    signin_type,
    share_state,
    inner_ip,
    row_number() over (partition by student_id,signin_time) as rn,
    dt
from zxoe_ods.ods_tbh_student_signin_record
where cast(dt as date)=${TD_DATE}
)
insert overwrite table zxoe_dwd.fact_tbh_student_signin_record partition(dt)
select
    id,
    normal_class_flag,
    time_table_id,
    class_id,
    student_id,
    signin_time,
    signin_date,
    inner_flag,
    signin_type,
    share_state,
    inner_ip,
    dt
from sign
where rn=1;



with leave_temp as (
select id,
       class_id,
       student_id,
       audit_state,
       audit_person,
       substr(audit_time,1,19) as audit_time,
       audit_remark,
       leave_type,
       leave_reason,
       substr(begin_time,1,19) as begin_time,
       begin_time_type,
       substr(end_time,1,19) as end_time,
       end_time_type,
       days,
       cancel_state,
       substr(cancel_time,1,19) as cancel_time,
       old_leave_id,
       leave_remark,
       valid_state,
       substr(create_time,1,19) as create_time,
       row_number() over (partition by student_id,create_time) as rn,
       dt
from
zxoe_ods.ods_student_leave_apply
where cast(dt as date)=${TD_DATE}
)
insert overwrite table zxoe_dwd.fact_student_leave_apply partition(dt)
select
    id,
    class_id,
    student_id,
    audit_state,
    audit_person,
    audit_time,
    audit_remark,
    leave_type,
    leave_reason,
    begin_time,
    begin_time_type,
    end_time,
    end_time_type,
    days,
    cancel_state,
    cancel_time,
    old_leave_id,
    leave_remark,
    valid_state,
    create_time,
    dt
from leave_temp
where rn=1;


with class_time_temp as(
select
    id,
    class_id,
    morning_template_id,
    morning_begin_time,
    morning_end_time,
    afternoon_template_id,
    afternoon_begin_time,
    afternoon_end_time,
    evening_template_id,
    evening_begin_time,
    evening_end_time,
    use_begin_date,
    use_end_date,
    substr(create_time,1,19) as create_time,
    create_person,
    remark,
    row_number() over (partition by class_id,create_time,create_person) as rn,
    dt
FROM
zxoe_ods.ods_tbh_class_time_table
where cast(dt as date)=${TD_DATE}
)
insert overwrite table zxoe_dwd.dim_tbh_class_time_table partition (dt)
select
    id,
    class_id,
    morning_template_id,
    morning_begin_time,
    morning_end_time,
    afternoon_template_id,
    afternoon_begin_time,
    afternoon_end_time,
    evening_template_id,
    evening_begin_time,
    evening_end_time,
    use_begin_date,
    use_end_date,
    create_time,
    create_person,
    remark,
    dt
from class_time_temp
where rn=1;


with course_temp as(
select
    id,
    base_id,
    class_id,
    class_date,
    content,
    teacher_id,
    teacher_name,
    job_number,
    classroom_id,
    classroom_name,
    is_outline,
    class_mode,
    is_stage_exam,
    is_pay,
    tutor_teacher_id,
    tutor_teacher_name,
    tutor_job_number,
    is_subsidy,
    answer_teacher_id,
    answer_teacher_name,
    answer_job_number,
    remark,
    create_time,
    row_number() over (partition by class_id,class_date,content) as rn,
    dt
from zxoe_ods.ods_course_table_upload_detail
where cast(dt as date)=${TD_DATE}
)
insert overwrite table zxoe_dwd.dim_course_table_upload_detail partition (dt)
select
    id,
    base_id,
    class_id,
    class_date,
    content,
    teacher_id,
    teacher_name,
    job_number,
    classroom_id,
    classroom_name,
    is_outline,
    class_mode,
    is_stage_exam,
    is_pay,
    tutor_teacher_id,
    tutor_teacher_name,
    tutor_job_number,
    is_subsidy,
    answer_teacher_id,
    answer_teacher_name,
    answer_job_number,
    remark,
    create_time,
    dt
from course_temp
where rn=1;

with count_temp as(
select
    id,
    school_id,
    subject_id,
    class_id,
    studying_student_count,
    studying_date,
    row_number() over (partition by class_id,studying_date) as rn,
    dt
from zxoe_ods.ods_class_studying_student_count
where cast(dt as date)=${TD_DATE}
)
insert overwrite table zxoe_dwd.dim_class_studying_student_count partition (dt)
select
    id,
    school_id,
    subject_id,
    class_id,
    studying_student_count,
    studying_date,
    dt
from count_temp
where rn=1;
"